<?php


namespace app\admin\service;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Cache;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Request;

class ExcelService
{
    /**
     * Request实例
     * @var Request
     */
    protected $request;
    //列KEY
    private static $colKey=[
        'A','B','C','D','E','F','G','H','I','J','K','L','M',
        'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
        'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
        'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
    ];
    //行宽
    private static $where=20;
    //行高
    private static $height=50;
    //设置style
    private static $contentStyle=[
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => '00000000'],
            ],
        ],
        'alignment'=>[
            'horizontal'=>\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            'vertical'=>\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
        ]
    ];
    //根据前端传的参，自动生成需要导出的数据列表
    public function getWhere($post){
        $filters = json_decode(html_entity_decode($post['filter']),true);
        $ops = json_decode(html_entity_decode($post['op']),true);
        $where=[];
        foreach ($filters as $key => $val) {
            $op = isset($ops[$key]) && !empty($ops[$key]) ? $ops[$key] : '%*%';
            switch (strtolower($op)) {
                case '=':
                    $where[] = [$key, '=', $val];
                    break;
                case '%*%':
                    $where[] = [$key, 'LIKE', "%{$val}%"];
                    break;
                case '*%':
                    $where[] = [$key, 'LIKE', "{$val}%"];
                    break;
                case '%*':
                    $where[] = [$key, 'LIKE', "%{$val}"];
                    break;
                case 'range':
                    [$beginTime, $endTime] = explode(' - ', $val);
                    $where[] = [$key, '>=', strtotime($beginTime)];
                    $where[] = [$key, '<=', strtotime($endTime)];
                    break;
                default:
                    $where[] = [$key, $op, "%{$val}"];
            }
        }
        return $where;
    }
    public function setSheetTitle($activeSheet,$title){
        $activeSheet->getCell(self::$colKey[0].'1')->setValue($title);
        $activeSheet->getStyle(self::$colKey[0].'1')->getFont()->setBold(true)->setSize(20);
        return $activeSheet;
    }
    public function setSheetHeader($activeSheet,$colData){
        $colCount=count($colData);
        for ($i=0;$i<$colCount;$i++){
            $activeSheet->getCell(self::$colKey[$i].'2')->setValue($colData[$i]);
            $activeSheet->getStyle(self::$colKey[$i].'2')->getFont()->setBold(true)->setSize(12);
            $activeSheet->getColumnDimension(self::$colKey[$i])->setAutoSize(true);
        }
        return $activeSheet;
    }
    public function setSheetContent($activeSheet,$list,$obj){
        $rowIndex=3;
        $maxColCount=0;
        foreach ($list as $k=>$v){
            $maxColCount=$maxColCount<count($v)?count($v):$maxColCount;
            for ($i=0;$i<count($v);$i++){
                $activeSheet->getCell(self::$colKey[$i].$rowIndex)->setValue($v[$i]);
            }
            $rowIndex++;
        }
        $endCell=self::$colKey[($maxColCount - 1)].(count($list) + 2);
        $activeSheet->mergeCells('A1:'.self::$colKey[($maxColCount - 1)].'1');
        $activeSheet->getStyle('A1:'.$endCell)->applyFromArray(self::$contentStyle);
        $activeSheet->getStyle('A1:'.$endCell)->getAlignment()->setWrapText(true);
        $activeSheet->getDefaultRowDimension()->setRowHeight(28);
        $writer = new Xlsx($obj);
        $filename=time();
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8;");
        header("Content-Disposition: inline;filename=\"{$filename}.xlsx\"");
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        exit();
    }

}